import pool from "@/lib/db";

const productModule = {
  getAll: async (limit = 10, offset = 0, db = pool) => {
    const sql = 'SELECT * FROM products ORDER BY created_at DESC LIMIT ? OFFSET ?';
    const [rows] = await db.query(sql, [limit, offset]);
    const countSql = 'SELECT COUNT(*) as total FROM products';
    const [[{ total }]] = await db.query(countSql);
    return { products: rows, total };
  },
  getById: async (id, db = pool) => {
    const sql = 'SELECT * FROM products WHERE id = ?';
    const [rows] = await db.query(sql, [id]);
    return rows[0] || null;
  },
  getBySlug: async (slug, db = pool) => {
    const sql = 'SELECT * FROM products WHERE slug = ?';
    const [rows] = await db.query(sql, [slug]);
    return rows[0] || null;
  },
  getAllInformationById: async (id, db = pool) => {
    const [products] = await db.query('SELECT * FROM products WHERE id = ?', [id]);
    if (!products.length) return null;
    const product = products[0];
    const [skus] = await db.query('SELECT * FROM product_skus WHERE product_id = ?', [id]);
    const [images] = await db.query('SELECT * FROM product_images WHERE product_id = ?', [id]);
    const [collections] = await db.query(`SELECT c.* FROM collections c JOIN collection_product cp ON c.id = cp.collection_id WHERE cp.product_id = ?`, [id]);
    return { product, skus, images, collections };
  },
  getAllInformationBySlug: async (slug, db = pool) => {
    const [products] = await db.query('SELECT * FROM products WHERE slug = ?', [slug]);
    if (!products.length) return null;
    const product = products[0];
    const [skus] = await db.query('SELECT * FROM product_skus WHERE product_id = ?', [product.id]);
    const [images] = await db.query('SELECT * FROM product_images WHERE product_id = ?', [product.id]);
    const [collections] = await db.query(`SELECT c.* FROM collections c JOIN collection_product cp ON c.id = cp.collection_id WHERE cp.product_id = ?`, [product.id]);
    return { product, skus, images, collections };
  },
  getSlugs: async (db = pool) => {
    const sql = 'SELECT slug FROM products WHERE slug IS NOT NULL';
    const [rows] = await db.query(sql);
    return rows.map(row => row.slug);
  },
  getImagesById: async (id, db = pool) => {
    const sql = 'SELECT * FROM product_images WHERE product_id = ?';
    const [rows] = await db.query(sql, [id]);
    return rows;
  },
  create: async ({ status, title, description, price, compare_price, stock, cover, sort, template, slug, meta_title, meta_description }, db = pool) => {
    const sql = 'INSERT INTO products (status, title, description, price, compare_price, stock, cover, sort, template, slug, meta_title, meta_description) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
    const values = [status, title, description, price, compare_price, stock, cover, sort, template, slug, meta_title, meta_description];
    const [result] = await db.query(sql, values);
    return result.insertId;
  },
  createImages: async (productId, images, db = pool) => {
    const sql = 'INSERT INTO product_images (product_id, url, alt, is_primary, sort) VALUES ?';
    const values = images.map(image => [ productId, image.url, image.alt || '', image.is_primary ?? 1, image.sort ?? 0 ]);
    const [result] = await db.query(sql, [values]);
    if (result.affectedRows === 0) { return false; }
    return result.affectedRows;
  },
  createCollections: async (productId, collectionIds, db = pool) => {
    if (!collectionIds || collectionIds.length === 0) return false;
    const sql = 'INSERT INTO collection_product (product_id, collection_id) VALUES ?';
    const values = collectionIds.map(collectionId => [ productId, collectionId ]);
    const [result] = await db.query(sql, [values]);
    if (result.affectedRows === 0) { return false; }
    return result.affectedRows;
  },
  update: async (id, { status, title, description, price, compare_price, stock, cover, sort, template, slug, meta_title, meta_description }, db = pool) => {
    const sql = 'UPDATE products SET status = ?, title = ?, description = ?, price = ?, compare_price = ?, stock = ?, cover = ?, sort = ?, template = ?, slug = ?, meta_title = ?, meta_description = ? WHERE id = ?';
    const values = [status, title, description, price, compare_price, stock, cover, sort, template, slug, meta_title, meta_description, id];
    const [result] = await db.query(sql, values);
    return result;
  },
  deleteById: async (id, db = pool) => {
    const sql = 'DELETE FROM products WHERE id = ?';
    const [result] = await db.query(sql, [id]);
    return result.affectedRows;
  },
  deleteImagesById: async (productId, db = pool) => {
    const sql = 'DELETE FROM product_images WHERE product_id = ?';
    const [result] = await db.query(sql, [productId]);
    return result.affectedRows;
  },
  deleteCollectionsById: async (productId, db = pool) => {
    const sql = 'DELETE FROM collection_product WHERE product_id = ?';
    const [result] = await db.query(sql, [productId]);
    return result.affectedRows;
  },
}

export default productModule;